<?php
/**
 * 日销量统计报表SKU (库存)
 * (model)
 */

namespace App\Models\SalesReport;

use App\Models\Basics\SysArea;
use App\Models\Basics\SysCountry;
use App\Models\Basics\SysPlat;
use App\Models\Inventory\PublicCn;
use App\Models\Product\ProductClassify;
use App\Models\Product\ProductSku;
use App\Util\User;
use Illuminate\Database\Eloquent\Model;

class DailySv extends Model
{
    /**
     * 日销量统计报表-SKU
     * @var string
     */
    protected $table = 'sr_daily_sv';

    protected $guarded = [];

    const SATE_REPORT_DATE = '2017-01-01';

    public $timestamps = false;

    public function sku(){
        return $this->belongsTo(ProductSku::class,'psku_id');
    }

    public function classify(){
        return $this->belongsTo(ProductClassify::class,'classify_id');
    }

    public function area(){
        return $this->belongsTo(SysArea::class,'area_id');
    }

    public function country(){
        return $this->belongsTo(SysCountry::class,'country_id');
    }
    public function plat(){
        return $this->belongsTo(SysPlat::class,'plat_id','plat_id');
    }

    //销量趋势
    public static $_salesStatus = [
        'hup' => '持升',
        'hd' => '持降',
        'wup' => '波升',
        'wd' => '波降',
    ];

    //销量权重
    public static $_salesWeighted = [
        'hup' => ['weighted3' => 0.25,'weighted7' => 0.25,'weighted15' => 0.25,'weighted30' => 0.25],
        'hd' => ['weighted3' => 0.25,'weighted7' => 0.25,'weighted15' => 0.25,'weighted30' => 0.25],
        'wup' => ['weighted3' => 0.40,'weighted7' => 0.30,'weighted15' => 0.20,'weighted30' => 0.10],
        'wd' => ['weighted3' => 0.20,'weighted7' => 0.20,'weighted15' => 0.30,'weighted30' => 0.30],
    ];

    /**
     * 销量状态
     * @param $avg3
     * @param $avg7
     * @param $avg15
     * @param $avg30
     * @return string
     */
    public static function getSalesStatus($avg3,$avg7,$avg15,$avg30){
        if($avg3 > $avg7 && $avg7 > $avg15 && $avg15 > $avg30){
            $salesStatus = 'hup';
        }elseif($avg3 < $avg7 && $avg7 < $avg15 && $avg15 < $avg30){
            $salesStatus = 'hd';
        }elseif(($avg3 + $avg7) >= ($avg15 + $avg30)){
            $salesStatus = 'wup';
        }elseif(($avg3 + $avg7) < ($avg15 + $avg30)){
            $salesStatus = 'wd';
        }else{
            $salesStatus = 'hup';
        }
        return $salesStatus;
    }

    /**
     * 取加权平均销量
     * @param $avg3
     * @param $avg7
     * @param $avg15
     * @param $avg30
     * @param string $salesStatus
     * @return float
     */
    public static function getWeightAvg($avg3,$avg7,$avg15,$avg30,$salesStatus = 'hup'){
        $sw = self::$_salesWeighted[$salesStatus];
        $weightAvg = $avg3 * $sw['weighted3'] + $avg7 * $sw['weighted7'] + $avg15 * $sw['weighted15'] + $avg30 * $sw['weighted30'];
        return round($weightAvg);
    }

    /**
     * FBA库存
     * @param $model
     * @return mixed
     */
    private static function _getFBAInventory($model){
        return $model->fba_instant_num + $model->fba_transit_num;
    }

    /**
     * 所有库存
     * @param $model
     * @return mixed
     */
    private static function _getAllInventory($model){
        return $model->fba_instant_num + $model->fba_transit_num +
            $model->cn_instant_num + $model->cn_transit_num +
            $model->os_instant_num + $model->os_transit_num;
    }

    /**
     * 库存系数 (模型对象参数)
     * @param $model
     * @return string
     */
    public static function getInvFactor($model){
        $fbaInv = self::_getFBAInventory($model);
        $allInv = self::_getAllInventory($model);
        $fbaInvFactor = $fbaInv > 0 && $model->avg > 0 ? numberFormat($fbaInv / $model->avg / 30,4) : 0;
        $allInvFactor = $allInv > 0 && $model->avg > 0 ? numberFormat($allInv / $model->avg / 30,4) : 0;
        return $fbaInvFactor .' & '.$allInvFactor;
    }

    /**
     * 库存系数 (非模型对象参数)
     * @param $fbaInstant
     * @param $fbaTransit
     * @param $cnInstant
     * @param $cnTransit
     * @param $osInstant
     * @param $osTransit
     * @param $avg
     * @return string
     */
    public static function getInvFactorString($fbaInstant,$fbaTransit,$cnInstant,$cnTransit,$osInstant,$osTransit,$avg){
        $fbaInv = $fbaInstant + $fbaTransit;
        $allInv = $fbaInv + $cnInstant + $cnTransit + $osInstant + $osTransit;
        $fbaInvFactor = $fbaInv > 0 && $avg > 0 ? numberFormat($fbaInv / $avg / 30,4) : 0;
        $allInvFactor = $allInv > 0 && $avg > 0 ? numberFormat($allInv / $avg / 30,4) : 0;
        return $fbaInvFactor .' & '.$allInvFactor;
    }

    /**
     * 销量报表模型
     * @param $countryId
     * @param $pskuId
     * @return \Illuminate\Database\Eloquent\Builder|Model|object|null
     */
    public static function getModelByCondition($countryId,$pskuId){
        return self::query()->where('country_id',$countryId)->where('psku_id',$pskuId)->first();
    }

    /**
     * 获取平均销量和库存的汇总
     * @param null $sku
     * @param null $platId
     * @param null $countryId
     * @param null $areaId
     * @param null $sellerId
     * @param null $classifyId
     * @param null $season
     * @param null $reportStatus
     * @return \Illuminate\Database\Eloquent\Builder|Model|object|null
     */
    public static function getAvgDayAndInventorySum($sku = null, $platId = null, $countryId = null, $areaId = null, $sellerId = null, $classifyId = null, $season = null, $reportStatus = null){
        $query = self::query()
            ->selectRaw('sum(avg3) as avg3,
            sum(avg7) as avg7,
            sum(avg15) as avg15,
            sum(avg30) as avg30,
            sum(avg) as avg,
            sum(fba_instant_num) as fba_instant_num,
            sum(fba_transit_num) as fba_transit_num,
            sum(cn_instant_num) as cn_instant_num,
            sum(cn_transit_num) as cn_transit_num,
            sum(os_instant_num) as os_instant_num,
            sum(os_transit_num) as os_transit_num');
        if ($sku) {
            $query->whereHas('sku', function ($query) use($sku){
                $query->where('psku_code', 'like', "%{$sku}%")
                    ->orWhere('psku_cn', 'like', "%{$sku}%");
            });
        }
        if ($platId) $query->where('plat_id', $platId);
        if ($countryId) $query->where('country_id', $countryId);
        if ($areaId) $query->where('area_id', $areaId);
        if ($sellerId) $query->where('seller_id', $sellerId);
        if ($classifyId) $query->where('classify_id', $classifyId);
        if ($season) {
            $query->whereHas('classify', function ($query) use($season){
                $query->where('season', $season);
            });
        }
        if ($reportStatus) {
            $query->whereHas('classify', function ($query) use($reportStatus){
                $query->where('report_status', $reportStatus);
            });
        }
        return $query->first();
    }

    /**
     * 检查查询报表时提交的截止日期
     * @param $lessThan
     * @return string
     */
    public static function checkLessThan($lessThan){
        if(strtotime($lessThan) > strtotime('-1 days')){
            $lessThan = date('Y-m-d', strtotime('-1 days'));
        }
        return $lessThan;
    }

    /**
     * 检查查询报表时提交的开始日期
     * @param $greaterThan
     * @param $lessThan
     * @return string
     */
    public static function checkGreaterThan($greaterThan, $lessThan){
        if(strtotime($greaterThan) > strtotime($lessThan)){
            $greaterThan = $lessThan;
        }
        return $greaterThan;
    }

    public static function headersField(){
        $fields = [];
        // $fields['area'] = ['title' => '站点','sumField' => false];
        // $fields['country'] = ['title' => '国家','sumField' => false];
        // $fields['seller'] = ['title' => '销售','sumField' => false];
        // $fields['sku'] = ['title' => 'SKU','sumField' => false];
        // $fields['sku_cn'] = ['title' => '产品名称','sumField' => false];
        // for($i = self::V_NUM; $i >= 1; $i--){
        //     $fields['day'.$i] = [
        //         'title' => date("m.d",strtotime("-$i days")),
        //         'sumField' => true
        //     ];
        // }
        $fields['avg3'] = ['title' => '3天AVG','sumField' => true];
        $fields['avg7'] = ['title' => '7天AVG','sumField' => true];
        $fields['avg15'] = ['title' => '15天AVG','sumField' => true];
        $fields['avg30'] = ['title' => '30天AVG','sumField' => true];
        // $fields['avg'] = ['title' => 'AVG','sumField' => false];
        // $fields['sales_status'] = ['title' => '销量趋势','sumField' => false];
        $fields['fba_instant_num'] = ['title' => 'FBA在库','sumField' => true];
        $fields['fba_transit_num'] = ['title' => 'FBA在途','sumField' => true];
        $fields['cn_instant_num'] = ['title' => '国内在库','sumField' => true];
        $fields['cn_transit_num'] = ['title' => '国内在途','sumField' => true];
        $fields['os_instant_num'] = ['title' => '海外仓在库','sumField' => true];
        $fields['os_transit_num'] = ['title' => '海外仓在途','sumField' => true];
        // $fields['inv_factor'] = ['title' => 'FBA&ALL库存系数','sumField' => false];
        return $fields;
    }

    public static function getDownloadData($re, $days, $startTime, $endTime, $timeMax){
        foreach($re as $item) {
            $temp = [];
            $temp['plat'] = $item->plat->title ?? '';
            $temp['area'] = $item->area->area ?? '';
            $temp['country'] = $item->country->country ?? '';
            $temp['psku_code'] = $item->sku->psku_code ?? '';
            $temp['psku_cn'] = $item->sku->psku_cn ?? '';
            $temp['classify'] = ProductClassify::getTitleById($item->classify_id);
            $temp['season'] = ProductClassify::$_season[$item->classify->season ?? 3];
            $temp['seller'] = User::getUsernameById($item->seller_id);
            $dayVolumeArray = DailyVolume::getSkuDaysVolume($startTime, $endTime, $item->plat_id, $item->psku_id, $item->country_id);
            $volume = 0;
            for($i = $days; $i >= 0; $i--){
                $dateTitle = date("Y-m-d", strtotime("$timeMax -$i day")); //日期格式 (2022-01-01)
                if(array_key_exists($dateTitle, $dayVolumeArray)){
                    $volume += $dayVolumeArray[$dateTitle]; //赋值,当期销量(当天)
                }
            }
            $temp['volume'] = $volume;
            //每日销量
            for($i = $days; $i >= 0; $i--){
                $dateTitle = date("Y-m-d", strtotime("$timeMax -$i day")); //日期格式 (2022-01-01)
                if(array_key_exists($dateTitle, $dayVolumeArray)){
                    $temp[$dateTitle] = $dayVolumeArray[$dateTitle]; //赋值,当期销量(当天)
                }else{
                    $temp[$dateTitle] = 0;
                }
            }
            $temp['avg3'] = round($item->avg3);
            $temp['avg7'] = round($item->avg7);
            $temp['avg15'] = round($item->avg15);
            $temp['avg30'] = round($item->avg30);
            $temp['avg'] = round($item->avg);
            $temp['sales_status'] = self::$_salesStatus[$item->sales_status];
            if ($item->plat_id == SysPlat::PLAT_AMAZON) {
                $temp['cnp_instant_num'] = PublicCn::getInstant($item->psku_id); //公共在库
            }else{
                $temp['cnp_instant_num'] = 0; //公共在库
            }
            $temp['fba_instant_num'] = $item->fba_instant_num;
            $temp['fba_transit_num'] = $item->fba_transit_num;
            $temp['cn_instant_num'] = $item->cn_instant_num;
            $temp['cn_transit_num'] = $item->cn_transit_num;
            $temp['os_instant_num'] = $item->os_instant_num;
            $temp['os_transit_num'] = $item->os_transit_num;
            $temp['inventory_num'] = $temp['fba_instant_num'] + $temp['fba_transit_num'] + $temp['cn_instant_num'] + $temp['cn_transit_num'] + $temp['os_instant_num'] + $temp['os_transit_num']; //库存和
            $temp['inv_factor'] = self::getInvFactor($item);
            yield $temp;
        }
    }
}
